'=====================================================================
'
'  File:    Program.vb for Multiple Active Result Sets (MARS) Sample
'  Summary: Main application which increases the prices for the most popular
'           bikes in order to demonstrate using the same connection to process
'           two SQL commands in parallel.
'  Date:    January 6, 2006
'
'---------------------------------------------------------------------
'
'   This file is part of the Microsoft SQL Server Code Samples.
'   Copyright (C) Microsoft Corporation.  All rights reserved.
'
'   This source code is intended only as a supplement to Microsoft
'   Development Tools and/or on-line documentation.  See these other
'   materials for detailed information regarding Microsoft code samples.
'
'   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
'   KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
'   IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'   PARTICULAR PURPOSE.
'
'======================================================= 

#Region "Using directives"

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Configuration
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

#End Region

Class Program
    'The factor to mulitply by the current price to get the new price for each color
    Private Const priceIncreases As String = "Black, 1.02, Silver, 1.01, Red, 1.05, Yellow, 1.02, Green, 1.03"
    'Given the name of a color, return the price increase factor as a SqlMoney object.
    Private Shared increaseDictionary As Dictionary(Of String, SqlMoney) _
        = New Dictionary(Of String, SqlMoney)(priceIncreases.Length)

    ''' <summary>
    '''     The Adventure Works Cycles corporation needs to increase the standard costs and list price
    '''     for its most popular bikes due to price increases for the paint used on those bikes.  This 
    '''     program demonstrates executing two commands at once on the same connection using the 
    '''     Multiple Active Result Sets (MARS) technology to implement that scenario.  Note that programmers
    '''     should always consider whether using a JOIN in a server side query or update would be more
    '''     efficient that using this style of programming.
    ''' </summary>
    Shared Sub Main()
        'Use the priceIncreases constant to initialize entries in the increaseDictionary collection.
        InitializeIncreaseDictionary()

        Dim myConnection As SqlConnection = Nothing
        Dim myTransaction As SqlTransaction = Nothing
        Dim popularBikesReader As SqlDataReader = Nothing
        Dim errorCount As Integer = 0

        Try
            'Initialize the command to get most popular bikes
            myConnection = New SqlConnection(My.Settings.Default.ConnectionString)
            Dim popularBikesCommand As SqlCommand = myConnection.CreateCommand()
            popularBikesCommand.CommandText = "usp_GetMostPopularBikes"
            popularBikesCommand.CommandType = CommandType.StoredProcedure

            myConnection.Open()

            'Make sure that either all database operations succeed, 
            'or all price increases are reverted to
            'what they were before this program is invoked.
            myTransaction = myConnection.BeginTransaction()
            popularBikesCommand.Transaction = myTransaction

            'Get the list of the most popular bikes
            popularBikesReader = popularBikesCommand.ExecuteReader()

            'Initialize the command to update the price of a product
            Dim updateCommand As SqlCommand = myConnection.CreateCommand()
            updateCommand.CommandText = "usp_UpdateProductPrice"
            updateCommand.CommandType = CommandType.StoredProcedure

            Dim productIDParameter As New SqlParameter("@ProductID", SqlDbType.Int)
            updateCommand.Parameters.Add(productIDParameter)
            Dim standardCostParameter As New SqlParameter("@StandardCost", SqlDbType.Money)
            updateCommand.Parameters.Add(standardCostParameter)
            Dim listPriceParameter As New SqlParameter("@ListPrice", SqlDbType.Money)
            updateCommand.Parameters.Add(listPriceParameter)

            updateCommand.Transaction = myTransaction

            While popularBikesReader.Read()
                Try
                    'Column two in the result set contains the color of the bike
                    Dim rateOfIncrease As SqlMoney = increaseDictionary(popularBikesReader.GetString(2))

                    'Column zero in the result set contains the product ID
                    productIDParameter.Value = popularBikesReader.GetInt32(0)

                    'Column three in the result set contains the current standard cost
                    standardCostParameter.Value = popularBikesReader.GetSqlMoney(3) * rateOfIncrease

                    'Column four in the result set contains the current list price
                    listPriceParameter.Value = popularBikesReader.GetSqlMoney(4) * rateOfIncrease
                    Console.Write("Updating product {0} to have new standard cost of {1} and new " & _
                        "list price of {2}... ", popularBikesReader.GetString(1), _
                        standardCostParameter.Value.ToString(), _
                        listPriceParameter.Value.ToString())

                    'Column 1 in the result set contains the name of the product
                    'On the same connection as the currently executing query for popular bikes, update
                    'the prices of one of the popular bikes based on its color and current prices.  This
                    'would not be possible in ADO.NET 1.0.
                    updateCommand.ExecuteNonQuery()
                    Console.WriteLine("Done.")
                Catch e As SqlException
                    Console.WriteLine("Failed! Exception: {0}", e.ToString())
                    errorCount += 1
                    Throw
                End Try
            End While
        Finally
            If Not (popularBikesReader Is Nothing) Then
                popularBikesReader.Close()
            End If

            If Not (myConnection Is Nothing) Then
                If Not (myTransaction Is Nothing) Then
                    If errorCount = 0 Then
                        myTransaction.Commit()
                        Console.WriteLine("All updates completed successfully.")
                    Else
                        myTransaction.Rollback()
                        Console.WriteLine("{0} errors occurred.  All updates reverted.", errorCount)
                    End If
                End If

                myConnection.Close()
            End If
        End Try
    End Sub

    ''' <summary>
    ''' Helper function which fills the dictonary containing the price increases for each color
    ''' </summary>
    Shared Sub InitializeIncreaseDictionary()
        'Initialize a dictionary which contains the price increase factors for each color
        Dim splitIncreases As String() = priceIncreases.Split(New Char() {","c})
        For i As Integer = 0 To (splitIncreases.Length - 1) Step 2
            increaseDictionary(splitIncreases(i).Trim()) = SqlMoney.Parse(splitIncreases((i + 1)).Trim())
        Next
    End Sub
End Class